package ca.llsutherland.squash.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import ca.llsutherland.squash.domain.Clock;
import ca.llsutherland.squash.domain.Court;
import ca.llsutherland.squash.domain.CourtStyle;
import ca.llsutherland.squash.domain.Gym;
import ca.llsutherland.squash.domain.helper.CourtsHelper;
import ca.llsutherland.squash.exceptions.ValidationException;
import ca.llsutherland.squash.utils.StringUtils;

public class CourtsDao extends BaseDao {

    private static CourtsDao instance = new CourtsDao();

    @Override
    public String getUnderlyingTableName() {
        return "COURTS";
    }

    public static CourtsDao getInstance() {
        return instance ;
    }

    public Court create(Court court) {
        String sql = "INSERT INTO " + getUnderlyingTableName() + " (NAME, LOCATION, COURT_STYLE_ID, SPECIAL_NOTES, DATE_CREATED, DATE_UPDATED, GYM_ID) VALUES ("
            + StringUtils.addSingleQuotes(court.getName()) + ", " 
            + StringUtils.addSingleQuotes(court.getLocation()) + ", " 
            + court.getCourtStyle().getId() + ", " 
            + StringUtils.addSingleQuotes(court.getSpecialNotes()) + ", " 
            + StringUtils.addSingleQuotes(court.getDateCreated().toString()) + ", " 
            + StringUtils.addSingleQuotes(court.getDateUpdated().toString()) + ", " 
            + court.getGym().getId() + ");";
        Long lastInsertedId = super.create(sql);
        return findById(lastInsertedId);
    }

    private Court findById(Long id) {
        String findIdByQuery = getFindByIdQuery(id);
        return buildCourt(findIdByQuery);
    }

    private Court buildCourt(String sql) {
        Court court = null;
        ResultSet rs = super.findByQuery(sql);
        try {
            if(rs.next()){
                String name = rs.getString("NAME");
                Clock dateCreated = new Clock(rs.getTimestamp("DATE_CREATED").getTime());
                Clock dateUpdated = new Clock(rs.getTimestamp("DATE_UPDATED").getTime());
                String location = rs.getString("LOCATION");
                String specialNotes = rs.getString("SPECIAL_NOTES");
                Long courtStyleId = rs.getLong("COURT_STYLE_ID");
                CourtStyle courtStyle = CourtStylesDao.getInstance().findById(courtStyleId);
                Gym gym = GymsDao.getInstance().findById(rs.getLong("GYM_ID"));
                Long id = Long.valueOf(rs.getLong("ID"));
                court = CourtsHelper.createNewCourt(name, dateCreated, dateUpdated, location, specialNotes, gym, courtStyle, id);
            }
            rs.close();
        } catch (SQLException e) {
            throw new ValidationException(e.getMessage() + " in buildGym()");
        } finally {
            rs = null;            
        }
        return court;
    }

}
